package com.situ.student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.JDBC.JDBCUtil;

public class StudentDmo {

	public int insert(Students stu) {
		String sql = "insert into Students(id,name,major)values(?,?,?)";
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement ps = null;
		if (conn == null)
			return 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, stu.getId());
			ps.setString(2, stu.getName());
			ps.setString(3, stu.getMajor());
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				conn.close();
				ps = null;
				conn = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
		return 0;
	}

	/**
	 * 根据逻辑主键删除记录
	 * 
	 * @param model
	 * @return
	 */
	public int deleteByCode(String Id) {
		String sql = "delete from Students where id = ?";
		Connection conn = JDBCUtil.getConnection();
		if (conn == null)
			return 0;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, Id);
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps);
		}
		return 0;
	}

	/**
	 * 根据逻辑主键修改记录其他全部字段
	 * 
	 * @param model
	 * @return
	 */
	public int update(Students stu) {
		String sql = "update Students set name=?,major=? where id=?";
		Connection conn = JDBCUtil.getConnection();
		if (conn == null)
			return 0;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, stu.getName());
			ps.setString(2, stu.getMajor());
			ps.setString(3, stu.getId());
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps);
		}
		return 0;
	}

	/**
	 * 根据多个查询条件 - 执行查询sql，返回结果构成的实体类集合
	 * 
	 */
	public List<Students> selectList(Students stu) {
		// 要进行字符串拼接，使用StringBuffer/StringBuilder
		StringBuffer sql = new StringBuffer("select id,name,major from Students ");
		List<Object> vals = appendWhere(sql, stu);
		Connection conn = JDBCUtil.getConnection();
		if (conn == null)
			return null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Students> list = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql.toString());
			for (int i = 0; i < vals.size(); i++)
				ps.setObject(i + 1, vals.get(i));
			rs = ps.executeQuery();
			Students p = null;
			while (rs.next()) {
				p = new Students(null, null, null);
				p.setId(rs.getString("id"));
				p.setName(rs.getString("name"));
				p.setMajor(rs.getString("major"));
				list.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps, rs);
		}
		return list;
	}

	private List<Object> appendWhere(StringBuffer sql, Students stu) {
		sql.append(" where 1=1 ");
		List<Object> vals = new ArrayList<>();
		String Id = stu.getId();
		if (Id != null && !Id.trim().isEmpty()) { // !code.trim().isEmpty()：不是空字符串（即空格一类的）
			sql.append(" and id = ? ");
			vals.add(Id.trim());
		}
		String name = stu.getName();
		if (name != null && !name.trim().isEmpty()) {
			sql.append(" and name = ? ");
			vals.add(name.trim());
		}
		String major = stu.getMajor();
		if (major != null) {
			sql.append(" and major = ? ");
			vals.add(major);
		}


		return vals;
	}
}
